package net.bat.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.CellType;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;
import net.bat.db.BaseDAOImpl;
import net.bat.util.Cfg;

import org.hibernate.metadata.ClassMetadata;
import org.hibernate.type.Type;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;




public class DefaultExportExcelControl extends HttpServlet{
	public static Integer LIMIT=null;
	public static String NAME_LIMIT="excel_limit";
	private BaseDAOImpl dao = null;
	
	public int getLimit(){
		if(LIMIT==null){
			String lstr =Cfg.getInitParameter(NAME_LIMIT);
			LIMIT = Integer.parseInt(lstr);
		}
		return LIMIT;
	}
	public BaseDAOImpl getDao() {
		if (dao == null)
			dao = (BaseDAOImpl) Cfg.getBean("BaseDAOImpl");
		return dao;
	}

	public void setDao(BaseDAOImpl dao) {
		this.dao = dao;
	}
	
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}
	
	public String[] getDateValues(String[] ps,Object so){
		String[] values = new String[ps.length];
		if(so.getClass().isArray()){
			Object[] soa = (Object[])so;
			for(int j=0; j<ps.length; j++){ 
				values[j]=soa[j].toString();
			}
		}else{
			BeanWrapper bw = new BeanWrapperImpl(so);
			for(int j=0; j<ps.length; j++){             //字段
				String p = ps[j];
				Object pv = bw.getPropertyValue(p);			
				if(pv!=null){
					String cn = pv.getClass().getName();
					if(cn.equals("java.sql.Clob"))
						pv = pv.toString();
					if(cn.equals("java.math.BigDecimal")){
						BigDecimal fv = (BigDecimal)pv;
						pv = fv.intValue();
					}
				}else{
					pv="";
				}
				//ro.getItems().put(prefix+p, pv);  //pv字段值
				values[j] = pv.toString();			
			}		
		}
		return values;
	}
	
	public void writeList(OutputStream os,List listobjs,String en,String[] flds)throws Exception{
		//add hearder row
		jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);// 创建可写工作薄
		jxl.write.WritableSheet ws = wwb.createSheet(en, 0);// 创建可写工作表
		jxl.write.Label labelCF1;
		ExcelMapping mapping=new ExcelMapping();
		List<Map<String,Map<String,String>>> list=mapping.addTableInfo();
		Map<String,Map<String,String>> map=list.get(0);
	
		Map<String,String> tabMap=map.get("dao.hb.PinPerson");
	
		for (int i = 0; i < flds.length; i++) {
			String str=tabMap.get(flds[i]);
			labelCF1 = new jxl.write.Label(i, 0, str);      //2 label					
			ws.addCell(labelCF1);			
			
		}
		//add data row
		for (int i = 0; i < listobjs.size(); i++) {           
			Object so= listobjs.get(i);
			if(so.getClass().isArray()){
				so = ((Object[])so)[0];
			}
			String values[];
			values = getDateValues(flds, so);
			for (int j = 0; j < flds.length; j++) {                    // 一条记录的所有字段							
				jxl.write.Label data1 = new jxl.write.Label(j, i+1, values[j]); //2 value
				ws.addCell(data1);							
			}
		}
		os.flush();
		wwb.write();
		wwb.close();
		os.close();
	}
	public void writeExcel(HttpServletResponse response,HttpServletRequest request) throws Exception{
		String tablename=request.getParameter("obj").toString();
		char type=request.getParameter("type").charAt(0);
		ClassMetadata cm = getDao().getClassMeta(tablename);
		String[] flds;
		String iden = cm.getIdentifierPropertyName();
		String sid= request.getParameter("sid");
		if(sid!=null){
			Type tp = cm.getIdentifierType();
			//此处修改因 Rinternet表的id为integer 
			if(tp.getName().equals("string")||tp.getName().equals("integer")){
				String[] sids = sid.split(",");
				StringBuffer sbuf = new StringBuffer();
				for(int i=0; i<sids.length; i++){
					if(i==0)
						sbuf.append("'"+sids[i]+"'");
					else
						sbuf.append(",'"+sids[i]+"'");
				}
				sid=sbuf.toString();
			}
		}
		List listobjs=null;
		String hql ="";
		if(type=='G'){
			String grp = request.getParameter("group");
			flds=new String[]{grp,"Count"};
			hql="select "+grp +",count("+grp+")  "
				+ request.getParameter("filter")+" group by "+grp;
			int top =Integer.parseInt(request.getParameter("top"));
			if(top<0)
				hql+=" order by count("+grp+") desc";
			else
				hql+=" order by count("+grp+")";
			listobjs=getDao().findTop(hql,Math.abs(top));
		}else{
			String[] pns = cm.getPropertyNames();
			flds = new String[pns.length+1];
			flds[0]=iden;
			System.arraycopy(pns, 0, flds, 1, pns.length);
			switch(type){
				case 'S'://一条数据)
					hql="from "+tablename+" where "+iden+" in ("+sid+") order by "+iden;
					break;
				case 'P'://一页数据
					hql="from "+tablename+" where "+iden+" in ("+sid+") order by "+iden;
					break;
				case 'A'://所有数据
					hql="from "+tablename+" order by "+iden;
					break;
				case 'F'://过滤条件
					hql=request.getParameter("filter");
					break;
			}
			int total_cout = getDao().getCountByQuery(hql,null);
			if(total_cout>getLimit()){
				response.setContentType("text/html;charset=UTF-8"); 
				response.getWriter().write("需要导出的记录数[<span style=\"color: rgb(255, 0, 0);\">"+total_cout+"</span>],超出当前允许的最大记录数[<span style=\"color: rgb(0, 255, 0);\">"+getLimit()+"</span>],<br>为避免长时间后台运算影响其他应用，请利用数据库工具执行导出任务");
				return;
			}
			listobjs = getDao().find(hql);
		}
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=UTF-8");		
		Date d=new Date();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd:hh:mm:ss");
		String filename=sdf.format(d).toString()+"_obj.xls";  //2010-03-03:02:43:39_obj.xls
		response.setHeader("Content-Disposition" ,"attachment;filename="+filename);                 				
		OutputStream os = response.getOutputStream();//将 WritableWorkbook 写入到输出流        
		writeList(os,listobjs,tablename,flds);
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {		
		try{
		    writeExcel(response,request);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}
